﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using System.Data.SqlClient;
using System.Web.Security;

namespace WebRole1
{
    public class dbManager
    {
        # region SQL common methods

        public static string conString = ConfigurationManager.ConnectionStrings["dbConnection"].ConnectionString;
        public static SqlConnection conn = null;
        private static SqlCommand SqlCommandConnection()
        {
            SqlCommand sql = new SqlCommand();
            sql.Connection = conn;
            return sql;
        }
        private void SqlOpenConnection()
        {

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = conString;
                conn.Open();

            }
            catch (SqlException ex)
            {
                throw ex;
            }
        }

        private static void CloseConnection()
        {
            conn.Close();
        }

        # endregion

        /// <summary>
        /// check if email has been used
        /// </summary>
        /// <param name="username"></param>
        /// <returns></returns>
        public bool emailExist(string username)
        {
            SqlOpenConnection();
            int count = 0;
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "SELECT *  FROM UserAccount WHERE email=@username";
            comm.Parameters.AddWithValue("@username", username);
            try
            {
                  dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    count++;
                }
                dr.Close(); dr.Dispose();
            }
            catch (Exception ex) { throw new Exception(ex.Message); }
            finally
            {
                CloseConnection();
            }
            return (count == 1) ? true : false;
        }

        /// <summary>
        /// login
        /// </summary>
        /// <param name="username"></param>
        /// <param name="password"></param>
        /// <returns></returns>
        public UserClass login(string username, string password)
        {
            UserClass user = new UserClass();
            SqlOpenConnection();
            string name = "";
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "SELECT name  FROM UserAccount WHERE email=@username AND Password=@password";
            comm.Parameters.AddWithValue("@username", username);
            comm.Parameters.AddWithValue("@password", hashPassword(password));
            try
            {
                  dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    name = (string)(dr["name"]);
                }
                dr.Close(); dr.Dispose();
            }
            catch (Exception ex) { throw new Exception(ex.Message); }
            finally
            {
                CloseConnection();
            }
            if (name != "")
            {
                user.Name = name;
                user.Roles = getRoles(username);
                return user;
            }
            return null;
        }
        SqlDataReader dr;
        public List<string> getRoles(string username)
        {
            List<string> roles = new List<string>();
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "SELECT  roles.userType  FROM user_roles, roles WHERE user_roles.roles = roles.uid AND user_roles.userAccount = @username";
            comm.Parameters.AddWithValue("@username", username);
            try
            {
                  dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    roles.Add((string)(dr["userType"]));
                }
                dr.Close(); dr.Dispose();
            }
            catch (Exception ex) { throw new Exception(ex.Message); }
            finally
            {
                CloseConnection();
            }
            return roles;
        }

        protected static string hashPassword(string password)
        {
            string result = "";
            result = FormsAuthentication.HashPasswordForStoringInConfigFile(password, "SHA1");
            return result;
        }

        /// <summary>
        /// create a new user. Note: Does not handle roles
        /// </summary>
        /// <param name="username"></param>
        /// <param name="password"></param>
        /// <param name="name"></param>
        /// <returns></returns>
        public bool createUser(string username, string password, string name)
        {
            int result = 0;
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "INSERT INTO UserAccount VALUES (@username, @password,@name)";
            comm.Parameters.AddWithValue("@username", username);
            comm.Parameters.AddWithValue("@password", hashPassword(password));
            comm.Parameters.AddWithValue("@name", name);
            try
            {
                result = comm.ExecuteNonQuery();
            }
            catch (Exception ex) { throw new Exception(ex.Message); }
            finally
            {
                CloseConnection();
            }
            if (result == 1)
            {
                return true;
            }
            return false;
        }

        /// <summary>
        /// Get the UID for the role. 
        /// </summary>
        /// <param name="userType"></param>
        /// <returns></returns>
        public string getRoleUID(string userType)
        {
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "SELECT uid  FROM roles WHERE userType = @userType";
            comm.Parameters.AddWithValue("@userType", userType);
            try
            {
                  dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    return (dr["uid"].ToString());
                }
                dr.Close(); dr.Dispose();
            }
            catch (Exception ex) { throw new Exception(ex.Message); }
            finally
            {
                CloseConnection();
            }
            return string.Empty;
        }

        /// <summary>
        /// add a new role for the user
        /// </summary>
        /// <param name="userType"></param>
        /// <param name="username"></param>
        /// <returns></returns>
        public bool addRole(string userType, string username)
        {
            string RoleUID = getRoleUID(userType);
            SqlOpenConnection();
            int result = 0;
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "INSERT INTO user_roles VALUES (@username, @RoleUID)";
            comm.Parameters.AddWithValue("@username", username);
            comm.Parameters.AddWithValue("@RoleUID", RoleUID);

            try
            {
                result = comm.ExecuteNonQuery();
            }
            catch (Exception ex) { throw new Exception(ex.Message); }
            finally
            {
                CloseConnection();
            }
            if (result == 1)
            {
                return true;
            }
            else
                return false;
        }

        public bool assignExerciseImage(string picture, string exercise)
        {
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "INSERT INTO exercise_exerciseImg VALUES (@picture,@exercise)";
            comm.Parameters.AddWithValue("@picture", picture);
            comm.Parameters.AddWithValue("@exercise", exercise);
            int result = comm.ExecuteNonQuery();
            return result == 1 ? true : false;
        }

        public int createExerciseImg(string name, string path, string description)
        {

            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "INSERT INTO exerciseImg VALUES (@name,@path,@description)";
            comm.Parameters.AddWithValue("@name", name);
            comm.Parameters.AddWithValue("@path", path);
            comm.Parameters.AddWithValue("@description", description);
            int uid = Convert.ToInt16(comm.ExecuteScalar());
          return uid;
        }
    }
}